#include "sqlmgr.h"
#include <QCoreApplication>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlDriver>
#include <QSqlError>
#include <exception>

using namespace std;


SqlMgr * SqlMgr::instance = nullptr;

SqlMgr::SqlMgr()
{

}

SqlMgr::~SqlMgr()
{

}

bool SqlMgr::startTransaction(QSqlDatabase &db)
{
    if(!db.transaction()){
        qDebug() << "开启事务失败:" << db.lastError().text();
        return false;
    }
    return true;
}

void SqlMgr::rollback(QSqlDatabase &db)
{
    if(!db.rollback()){
        qDebug() << "事务回滚失败:" << db.lastError().text();
    }
}

void SqlMgr::commit(QSqlDatabase &db)
{
    if(!db.commit()){
        qDebug() << "提交事务失败:" << db.lastError().text();
    }
}

void SqlMgr::init()
{
    this->m_db = QSqlDatabase::addDatabase("QSQLITE");
    //db.setHostName("acidalia");
    //db.setDatabaseName("book");
    //db.setUserName("mojito");
    //db.setPassword("J0a1m8");

    QString databaseName = QCoreApplication::applicationDirPath() + "/db/book.db";
    qDebug() << databaseName;

    this->m_db.setDatabaseName(databaseName);

    bool ok = this->m_db.open();
    qDebug() << "ok=" << ok;
}

void SqlMgr::test()
{
    qDebug() << "test...";
}

bool SqlMgr::login(const QString& strUser, const QString& strPass)
{
    QString loginSql = QString("SELECT * FROM user WHERE username='%1' and password='%2'").arg(strUser).arg(strPass);

    qDebug() << loginSql;

    QSqlQuery sqlQuery(loginSql, this->m_db);

    QSqlRecord sqlRecord = sqlQuery.record();

    QSqlDriver * sqlDriver = this->m_db.driver();
    bool supportQuerySize = sqlDriver->hasFeature(QSqlDriver::QuerySize);
    qDebug() << "是否支持返回总记录数:" << supportQuerySize;


    //获取的是记录的行数
    int rows = sqlQuery.size();

    //获取的是记录的列数
    int cols = sqlRecord.count();
    qDebug() << "查询出的记录数量:" << rows << ", 列数:" << cols;

    qDebug() << "查询错误信息:" << sqlQuery.lastError().text();

    bool loginFlag = false;

    while(sqlQuery.next()){
        int userid = sqlQuery.value(0).toInt();
        QString username = sqlQuery.value(1).toString();
        QString password = sqlQuery.value("password").toString();
        QString nickname = sqlQuery.value("nickname").toString();
        QString auth = sqlQuery.value("auth").toString();
        QString department = sqlQuery.value("department").toString();
        QString grade = sqlQuery.value("grade").toString();

        QString resultStr = QString("userid=%1, username=%2, "
                                    "password=%3, nickname=%4, "
                                    "auth=%5, department=%6, "
                                    "grade=%7")
                .arg(userid)
                .arg(username)
                .arg(password)
                .arg(nickname)
                .arg(auth)
                .arg(department)
                .arg(grade);
        qDebug() << resultStr;
        loginFlag = true;
    }

    return loginFlag;


}

QVector<QStringList> SqlMgr::getUsers(QString strCondition)
{
    QString sql = QString("SELECT * FROM user %1").arg(strCondition);

    qDebug() << sql;

    QSqlQuery sqlQuery(sql, this->m_db);

    QSqlRecord sqlRecord = sqlQuery.record();

    QSqlDriver * sqlDriver = this->m_db.driver();
    bool supportQuerySize = sqlDriver->hasFeature(QSqlDriver::QuerySize);
    qDebug() << "是否支持返回总记录数:" << supportQuerySize;


    //获取的是记录的行数
    int rows = sqlQuery.size();

    //获取的是记录的列数
    int cols = sqlRecord.count();
    qDebug() << "查询出的记录数量:" << rows << ", 列数:" << cols;

    qDebug() << "查询错误信息:" << sqlQuery.lastError().text();

    QVector<QStringList> vector;

    /*
    while(sqlQuery.next()){

        QStringList stringList;
        stringList.clear();

        int userid = sqlQuery.value(0).toInt();
        QString username = sqlQuery.value(1).toString();
        QString password = sqlQuery.value("password").toString();
        QString nickname = sqlQuery.value("nickname").toString();
        QString auth = sqlQuery.value("auth").toString();
        QString department = sqlQuery.value("department").toString();
        QString grade = sqlQuery.value("grade").toString();


        stringList << QString::number(userid);
        stringList << username;
        stringList << password;
        stringList << nickname;
        stringList << auth;
        stringList << department;
        stringList << grade;

        vector.push_back(stringList);
    }
    */

    while(sqlQuery.next()){
        QStringList stringList;
        stringList.clear();

        for(int i = 0; i < cols; i++){
            QString value = sqlQuery.value(i).toString();
            stringList << value;
        }
        vector.push_back(stringList);
    }
    return vector;
}

void SqlMgr::addUser(QVector<QStringList> userList)
{

    this->startTransaction(this->m_db);

    QSqlQuery sqlQuery(this->m_db);

    QString insertSql = QString("insert into user(username, password, nickname, auth, department, grade) values(?, ?, ?, ?, ?, ?)");

    //chopped(4) 去掉字符串末尾的n个字符

    int size = userList.size();
    for(int i = 0; i < size; i++){
        QStringList valueList = userList.value(i);

        QString username = valueList[0];
        QString password = valueList[1];
        QString nickname = valueList[2];
        QString auth = valueList[3];
        QString department = valueList[4];
        QString grade = valueList[5];

        sqlQuery.clear();

        sqlQuery.prepare(insertSql);
        sqlQuery.bindValue(0, username);
        sqlQuery.bindValue(1, password);
        sqlQuery.bindValue(2, nickname);
        sqlQuery.bindValue(3, auth);
        sqlQuery.bindValue(4, department);
        sqlQuery.bindValue(5, grade);

        qDebug() << "执行的sql=" << sqlQuery.lastQuery();
        sqlQuery.exec();
    }

    this->commit(this->m_db);


}

bool SqlMgr::delUser(QString strId)
{

    //目前异常和事务都不起作用
    try{

        bool startFlag = startTransaction(this->m_db);
        if(!startFlag){
            return false;
        }

        QString sql = QString("DELETE FROM user WHERE userid=%1").arg(strId.toInt());
        qDebug() << sql;
        QSqlQuery sqlQuery(this->m_db);
        bool flag = sqlQuery.exec(sql);

        if(!flag){
            qDebug() << "查询错误信息:" << sqlQuery.lastError().text();
            return false;
        }

        this->commit(this->m_db);

        //int a = 10 / 0;

        return true;
    }catch(QException & e){
        qDebug() << "出现异常信息:" << e.what();
        this->rollback(this->m_db);
    }catch(const std::exception & e){
        qDebug() << "===出现异常信息:" << e.what();
        this->rollback(this->m_db);
    }

    return false;

}

QVector<QStringList> SqlMgr::getBooks(QString strCondition)
{
    QSqlQuery sqlQuery(this->m_db);

    QString sql("SELECT * FROM book WHERE 1=1 ");
    if("" != strCondition){
        sql.append(QString( "and name like '%%1%'").arg(strCondition));
    }

    qDebug()<< "sql=" << sql;

    bool flag = sqlQuery.exec(sql);
    QVector<QStringList> vector;
    if(flag){
        int cols = sqlQuery.record().count();
        while(sqlQuery.next()){
            QStringList stringList;
            for(int i = 0; i < cols; i++){
                QString value = sqlQuery.value(i).toString();
                stringList << value;
            }
            vector.push_back(stringList);
        }
    }
    return vector;
}

QMap<QString, QString> SqlMgr::findBook(int bookid)
{
    QSqlQuery sqlQuery(this->m_db);
    QString sql = "SELECT * FROM book where bookid=?";

    sqlQuery.prepare(sql);
    sqlQuery.bindValue(0, bookid);

    QMap<QString, QString> * bookMap = Q_NULLPTR;

    if(!sqlQuery.exec()){
        qDebug() << "新增书籍失败:" << sqlQuery.lastError().text();
        return *bookMap;
    }

    bookMap = new QMap<QString, QString>();

    if(sqlQuery.next()){
        QSqlRecord record = sqlQuery.record();
        QString name = record.value("name").toString();
        QString cnt = record.value("cnt").toString();
        QString type1 = record.value("type1").toString();
        QString type2 = record.value("type2").toString();
        QString type3 = record.value("type3").toString();
        QString price = record.value("price").toString();

        bookMap->insert("name", name);
        bookMap->insert("cnt", cnt);
        bookMap->insert("type1", type1);
        bookMap->insert("type2", type2);
        bookMap->insert("type3", type3);
        bookMap->insert("price", price);
    }

    return *bookMap;
}

bool SqlMgr::addBook(QMap<QString, QString> & bookMap)
{
    QSqlQuery sqlQuery(this->m_db);
    QString sql = "insert into book(name, cnt, type1, type2, type3,price) values(?,?,?,?,?,?)";

    QString name = bookMap.value("name");
    QString cnt = bookMap.value("cnt");
    QString type1 = bookMap.value("type1");
    QString type2 = bookMap.value("type2");
    QString type3 = bookMap.value("type3");
    QString price = bookMap.value("price");

    sqlQuery.prepare(sql);
    sqlQuery.bindValue(0, name);
    sqlQuery.bindValue(1, cnt.toInt());
    sqlQuery.bindValue(2, type1);
    sqlQuery.bindValue(3, type2);
    sqlQuery.bindValue(4, type3);
    sqlQuery.bindValue(5, price);

    if(!sqlQuery.exec()){
        qDebug() << "新增书籍失败:" << sqlQuery.lastError().text();
        return false;
    }
    return true;
}

bool SqlMgr::updateBook(QMap<QString, QString> & bookMap)
{
    QSqlQuery sqlQuery(this->m_db);
    QString sql = "update book set name=?, cnt=?, type1=?, type2=?, type3=?, price=? where bookid=?";

    QString bookid = bookMap.value("bookid");
    QString name = bookMap.value("name");
    QString cnt = bookMap.value("cnt");
    QString type1 = bookMap.value("type1");
    QString type2 = bookMap.value("type2");
    QString type3 = bookMap.value("type3");
    QString price = bookMap.value("price");

    sqlQuery.prepare(sql);
    sqlQuery.bindValue(0, name);
    sqlQuery.bindValue(1, cnt.toInt());
    sqlQuery.bindValue(2, type1);
    sqlQuery.bindValue(3, type2);
    sqlQuery.bindValue(4, type3);
    sqlQuery.bindValue(5, price);
    sqlQuery.bindValue(6, bookid.toInt());

    if(!sqlQuery.exec()){
        qDebug() << "更新书籍失败:" << sqlQuery.lastError().text();
        return false;
    }
    return true;
}


void SqlMgr::delBook(QString strId)
{

}

QString SqlMgr::returnBook(QString strUserId, QString strBookId)
{

}

bool SqlMgr::borrowBook(QString strUserId, QString strBookId)
{
    QSqlQuery sqlQuery(this->m_db);
    QString sql = "insert into record(userid, bookid, start) values(?, ?, ?)";

    QDateTime nowDateTime = QDateTime::currentDateTime();
    QString startTime = nowDateTime.toString("yyyy-MM-dd hh:mm:ss");

    sqlQuery.prepare(sql);
    sqlQuery.bindValue(0, strUserId.toInt());
    sqlQuery.bindValue(1, strBookId.toInt());
    sqlQuery.bindValue(2, startTime);

    if(!sqlQuery.exec()){
        qDebug() << "借阅图书失败:" << sqlQuery.lastError().text();
        return false;
    }
    return true;
}

QVector<QStringList> SqlMgr::getRecords(QString strCondition)
{

}

void SqlMgr::clearRecords()
{

}
